﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using Newtonsoft.Json;
using PDAMain.Entity;


namespace PDAMain.DAL.Dao
{
    class InspectionWorkDao : BaseDao
    {
        /// <summary>
        /// 获取待处理巡检工单
        /// </summary>
        /// <returns></returns>
        public DataSet list()
        {
            DataSet inspectionListDt = new DataSet();
            string str_Sql = "select ASSIGNMENTID,WONUM,DESCRIPTION,LOCDESCRIPTION,case STATUS when '1' then '处理中' when '2' then '转单' when '3' then '报修' when '9' then '已处理' else '无法识别' end as STATUS  , ASSETNUM from InspectionWork where STATUS='1'";
            inspectionListDt = base.executeDataSet(str_Sql);
            return inspectionListDt;
        }
        /// <summary>
        /// 查询——查询符合条件的巡检工单
        /// </summary>
        /// <param name="status">状态</param>
        /// <param name="date">时间</param>
        /// <returns></returns>
        public DataSet select(string status, string date)
        {
            this.updateDataFromWebservice();
            DataSet inspectionSelDt = new DataSet();
            pdaWebReference.PdaService pda = base.getPdaWebService();
            string strData = "";
            if (date == string.Empty)
                strData = pda.listCheckWorkOrder(Identity.dutyPerson);
            else if (date != "")
                strData = pda.queryCheckWorkOrder(status, date, DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss"), Identity.dutyPerson);
            if (strData == string.Empty)
                return null;
            inspectionSelDt = base.convertToDataSet(strData);
            return inspectionSelDt;
        }
        public string dataNum()
        {
            this.updateDataFromWebservice();
            string num = "0";
            string str_Sql = "select count(*) from InspectionWork where STATUS='1'";
            num = base.executeDataSet(str_Sql).Tables[0].Rows[0][0].ToString();
            return num;

        }
        /// <summary>
        /// 获取主表详细信息
        /// </summary>
        /// <param name="inspectionID">巡检工单号</param>
        /// <returns></returns>
        public DataSet getDetail(string inspectionID)
        {
            DataSet inspectionDetailDt = new DataSet();
            string str_Sql = "select * from InspectionWork where ASSIGNMENTID='" + inspectionID + "'";
            inspectionDetailDt = base.executeDataSet(str_Sql);
            return inspectionDetailDt;
        }

        /// <summary>
        /// 获取从表详细信息
        /// </summary>
        /// <param name="inspectionID">巡检工单号</param>
        /// <returns></returns>
        public DataSet getTaskDetail(string inspectionID)
        {
            DataSet inspectionDetailDt = new DataSet();
            string str_Sql = "select * from inspectionWorkDetail where ASSIGNMENTID='" + inspectionID + "'";
            inspectionDetailDt = base.executeDataSet(str_Sql);
            return inspectionDetailDt;
        }

        /// <summary>
        /// 巡检工单处理完成，报修，现场工单均调用此方法更改工单状态
        /// </summary>
        /// <param name="inspectionID">巡检工单编号</param>
        /// <param name="status">巡检工单状态</param>1表示“处理中”，3表示“报修”，9表示“已完成”
        /// <param name="time">处理时间</param>
        /// <param name="remark">备注</param>
        /// <param name="description">问题描述</param>
        public bool deal(string inspectionID, string status, string time, string remark)
        {
            //此處處理狀態需同時在本地數據庫和Web端更新狀態
            bool successFlag = false;
            if (status == string.Empty)
                return successFlag;

            bool Flag = false;
            //鏈接webService接口
            pdaWebReference.PdaService pda = base.getPdaWebService();
            try
            {
                bool flagSub = false;
                Flag = pda.updateCheckWorkOrder(inspectionID, status, remark, time, Identity.dutyPerson);
                string str_Sql = "";

                //Flag为true(webservice更新成功)，则删除本地该条数据，为False(webservice更新失败)，则更新更新数据
                if (!Flag)
                    str_Sql = "update InspectionWork set status ='" + status + "', "
                             + " remark ='" + remark + "', "
                             + " STATUSDATE='" + time + "', "
                             + "' where ASSIGNMENTID ='" + inspectionID + "'";
                else
                {
                    str_Sql = "delete from InspectionWork where ASSIGNMENTID='" + inspectionID + "'";
                    flagSub = true;
                }
                Flag = base.executeNonQuery(str_Sql);
                if (flagSub)
                    base.executeNonQuery("delete from inspectionWorkDetail where ASSIGNMENTID='" + inspectionID + "'");
                return Flag;
            }
            catch
            {
                string str_Sql = "update InspectionWork set status ='" + status + "',"
                             + "remark ='" + remark
                             + "STATUSDATE='" + time + "',"
                             + "' where ASSIGNMENTID ='" + inspectionID + "'";
                Flag = base.executeNonQuery(str_Sql);
                return Flag;
            }
        }

        /// <summary>
        /// 同步时判断本地数据库是否相同的记录，有则不同步
        /// </summary>
        /// <param name="assignmentid">维修工单编号</param>
        /// <param name="type">类型</param>
        /// <returns></returns>
        public bool verifyData(string assignmentid, string type)
        {
            bool result = false;
            string strsql = "";
            //从服务器同步主表数据至PDA
            if (type == "main")
                strsql = "select 1 from InspectionWork where ASSIGNMENTID='" + assignmentid + "'";
            //从服务器同步从表数据至PDA
            else if (type == "sub")
                strsql = "select 1 from inspectionWorkDetail where ASSIGNMENTID='" + assignmentid + "'";
            DataSet ds = base.executeDataSet(strsql);
            try
            {
                if (ds.Tables[0].Rows.Count == 0)
                    result = true;
                return result;
            }
            catch
            {
                return false;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="status">状态</param>
        /// <param name="dateFrom">起始时间</param>
        /// <param name="dateEnd">结束时间</param>
        /// <param name="dutyperson">负责人</param>
        /// <returns></returns>
        public void insertData(string status, string dateFrom, string dateEnd, string dutyperson)
        {
            pdaWebReference.PdaService pda = base.getPdaWebService();
            string strResult = pda.listCheckWorkOrder(Identity.dutyPerson);

            List<Dictionary<string, string>> list = getDataList(strResult);
            string str_sql = "";
            int Count = 0;

            bool Flag = false;
            for (int i = 0; i < list.Count; i++)
            {
                if (verifyData(list[i]["ASSIGNMENTID"].ToString(), "main"))
                {
                    //插入主表数据
                    str_sql = "insert into InspectionWork values('" + list[i]["ASSIGNMENTID"].ToString() + "','"
                        + list[i]["WONUM"].ToString() + "','" + list[i]["DESCRIPTION"].ToString() + "','"
                        + list[i]["LOCATION"].ToString() + "','" + list[i]["LOCDESCRIPTION"].ToString() + "','"
                        + list[i]["ASSETNUM"].ToString() + "','" + list[i]["ASSDESCRIPTION"].ToString() + "','"
                        + list[i]["STATUS"].ToString() + "','" + list[i]["REMARK"].ToString() + "','"
                        + list[i]["STATUS"].ToString() + "')";
                    Flag = base.executeNonQuery(str_sql);
                }
                //主表数据插进去之后插入从表数据
                if (verifyData(list[i]["ASSIGNMENTID"].ToString(), "sub") && Flag)
                {
                    string subResult = pda.listCheckTaskByAssignmentId(list[i]["ASSIGNMENTID"].ToString());
                    List<Dictionary<string, string>> sublist = getDataList(subResult);
                    for (int j = 0; j < sublist.Count; j++)
                    {
                        str_sql = "insert into inspectionWorkDetail values('" + list[i]["ASSIGNMENTID"].ToString() + "','"
                            + list[i]["TASKID"].ToString() + "','" + list[i]["DESCRIPTION"].ToString() + "','"
                            + list[i]["STATUS"].ToString() + "')";
                        base.executeNonQuery(str_sql);
                    }
                }
            }
        }

        /// <summary>
        /// 同步數據
        /// </summary>
        /// <param name="dr"></param>
        public bool insertData(DataRow dr)
        {
            bool successFlag = false;
            string str_Sql = "insert into CustomerWork(ASSIGNMENTID,WONUM,DESCRIPTION,LOCATION,LOCDESCRIPTION,ASSETNUM,ASSDESCRIPTION,STATUS,remark,STATUSDATE) values("
                + "'" + dr["ASSIGNMENTID"] + "','" + dr["WONUM"] + "','"
                + dr["DESCRIPTION"] + "','" + dr["LOCATION_ID"] + "','"
                + dr["LOC_DESCRIPTION"] + "','" + dr["ASSET_NO"] + "','"
                + dr["ASSET_DESCRIPTION"] + "','" + dr["STATUS"] + "','','')";
            try
            {
                successFlag = base.executeNonQuery(str_Sql);
                return successFlag;
            }
            catch
            {
                return successFlag;
            }
        }

        /// <summary>
        /// 通过webservice同步PDA数据
        /// </summary>
        public void updateDataFromWebservice()
        {
            //同步數據
            //從Webservice同步數據
            DataTable dt = new DataTable();
            pdaWebReference.PdaService pda = base.getPdaWebService();
            string str = pda.listCheckWorkOrder(Identity.dutyPerson);
            if (str == string.Empty)
                return;
            DataSet ds = base.convertToDataSet(str);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return;
            dt = ds.Tables[0];
            string str_sql = "";
            bool Flag = false;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //驗證系統中是否存在該筆數據
                string strAssignmentid = dt.Rows[i]["ASSIGNMENTID"].ToString();
                if (verifyData(strAssignmentid, "main"))
                {
                    //插入主表数据
                    str_sql = "insert into InspectionWork values('" + dt.Rows[i]["ASSIGNMENTID"].ToString() + "','"
                        + dt.Rows[i]["WONUM"].ToString() + "','" + dt.Rows[i]["DESCRIPTION"].ToString() + "','"
                        + dt.Rows[i]["LOCATIONID"].ToString() + "','" + dt.Rows[i]["LOCDESCRIPTION"].ToString() + "','"
                        + dt.Rows[i]["ASSETNO"].ToString() + "','" + dt.Rows[i]["ASSETDESCRIPTION"].ToString() + "','"
                        + dt.Rows[i]["STATUS"].ToString() + "','" + dt.Rows[i]["WORKCOMMENT"].ToString() + "','')";
                    Flag = base.executeNonQuery(str_sql);
                }
                //主表数据插进去之后插入从表数据
                if (verifyData(strAssignmentid, "sub") && Flag)
                {
                    string subResult = pda.listCheckTaskByAssignmentId(strAssignmentid);
                    DataSet dsSub = base.convertToDataSet(subResult);
                    /* */
                    if (dsSub == null)
                        continue;
                    DataTable dtSub = dsSub.Tables[0];
                    for (int j = 0; j < dtSub.Rows.Count; j++)
                    {
                        try
                        {
                            str_sql = "insert into inspectionWorkDetail values('" + dtSub.Rows[j]["ASSIGNMENTID"].ToString() + "','"
                                + dtSub.Rows[j]["TASKID"].ToString() + "','" + dtSub.Rows[j]["DESCRIPTION"].ToString() + "','"
                                + dtSub.Rows[j]["STATUS"].ToString() + "')";
                        }
                        catch (Exception ex)
                        {

                        }
                        base.executeNonQuery(str_sql);
                    }
                }
            }/**/
        }

        /// <summary>
        /// 状态转换
        /// </summary>
        private static string[,] ListArray = new string[,] {
        {"1","处理中"},
        {"2","转单"},
        {"3","报修"},
        {"9","已处理"}
        };

        /// <summary>
        /// 通过code获取描述
        /// </summary>
        /// <param name="code">code</param>
        /// <returns></returns>
        public string getShow(string code)
        {
            string strResult = string.Empty;
            if (code == string.Empty)
                return strResult;
            for (int i = 0; i < ListArray.GetLength(0); i++)
            {
                if (code == ListArray.GetValue(i, 0).ToString())
                {
                    strResult = ListArray.GetValue(i, 1).ToString();
                    break;
                }
            }
            return strResult;
        }

        public List<Dictionary<string, string>> getDataList(string strlist)
        {
            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            JsonResult result = JsonConvert.DeserializeObject<JsonResult>(strlist);
            if (result.message == "")
            {
                list = result.data;
            }
            else
                list = null;
            return list;
        }

        public DataSet select()
        {
            DataSet ds = null;
            string str_Sql = "select * from InspectionWork";
            ds = base.executeDataSet(str_Sql);
            return ds;
        }

        public bool InspectionDrop()
        {
            bool result = false;
            string str_Sql = "drop table InspectionWork";
            string str_Sql2 = "drop table inspectionWorkDetail";
            if (base.executeNonQuery(str_Sql) && base.executeNonQuery(str_Sql2))
            {
                result = true;
            }
            return result;
        }

        public DataSet InspectionSelect(string strType)
        {
            DataSet ds = null;
            string str_Sql = "select ASSIGNMENTID,WONUM as woNum,DESCRIPTION as description,LOCDESCRIPTION as locDescription,case STATUS when '1' then '处理中' when '2' then '转单' when '3' then '报修' when '9' then '已处理' else '无法识别' end as status,STATUSDATE from InspectionWork where STATUS=" + strType + "";
            ds = base.executeDataSet(str_Sql);
            return ds;
        }
    }
}
